package com.feihao.zhou.demo02Test.dao;

import com.feihao.zhou.demo02Test.domain.Student;
import com.feihao.zhou.demo02Test.utils.JDBCUtils;
import org.junit.Test;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    @Override
    public List<Student> findAll() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Student> list = null;
        try {

            //获取连接
            connection = JDBCUtils.getConnection();  //静态代码块自动注册驱动
            //获取执行者对象
            preparedStatement = connection.prepareStatement("SELECT * from student");

            /*//操作事务：
            connection.commit();*/
            //执行sql
            resultSet = preparedStatement.executeQuery();

            //创建集合：
            list = new ArrayList<>();
            //处理结果：
            while (resultSet.next()) {
                Integer id = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                //封装学生：
                Student student = new Student(id, name, age, birthday);
                list.add(student);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    @Override
    public Student findById(Integer id) {
        Student student = null;


        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            /*//1.注册驱动：
            Class.forName("com.mysql.jdbc.Driver");*/
            //2.获取连接
            connection = JDBCUtils.getConnection();
            //3.获取执行对象
            preparedStatement = connection.prepareStatement("SELECT * FROM student WHERE sid = '" + id + "'");

            //4.执行sql
            resultSet = preparedStatement.executeQuery();

            //处理结果：
            while (resultSet.next()) {
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                student = new Student(sid, name, age, birthday);
            }
            //5.施放资源：
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
        return student;
    }

    @Override
    public int insert(Student stu) {
        int result = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            //1.注册驱动：

            /* Class.forName("com.mysql.jdbc.Driver");*/
            //2.获取连接

            connection = JDBCUtils.getConnection();
            //3.获取执行对象
            java.util.Date birthday1 = stu.getBirthday();
            String bir = new SimpleDateFormat("yyyy-MM-dd").format(birthday1);
            String sql = "INSERT INTO student VALUES ('" + stu.getSid() + "','" + stu.getName() + "','" + stu.getAge() + "','" + bir + "')";
            preparedStatement = connection.prepareStatement(sql);

            //4.执行sql
            result = preparedStatement.executeUpdate();


            //5.施放资源：
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCUtils.close(preparedStatement, connection);
        }
        return result;
    }

    @Override
    public int update(Integer sid) {
        int result = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            //1.注册驱动：

            /* Class.forName("com.mysql.jdbc.Driver");*/
            //2.获取连接

            connection = JDBCUtils.getConnection();
            //3.获取执行对象

            String sql = "UPDATE student set NAME = '哈哈哈' WHERE sid ='" + sid + "'";
            preparedStatement = connection.prepareStatement(sql);

            //4.执行sql
            result = preparedStatement.executeUpdate();


            //5.施放资源：
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCUtils.close(preparedStatement, connection);
        }
        return result;
    }

    @Override

    public int delete(Integer id) {
        int result = 0;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //注册驱动：
            /*Class.forName("com.mysql.jdbc.Driver");*/

            //获取连接
            connection = JDBCUtils.getConnection();

            //获取执行对象：
            //执行的sql：delete * from student where sid = id;
            String sql = "DELETE FROM student WHERE sid = '" + id + "'";
            preparedStatement = connection.prepareStatement(sql);
            //执行sql：
            result = preparedStatement.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtils.close(preparedStatement, connection);
        }
        //处理：
        return result;
    }
}
